from PyQt5 import QtCore, QtGui, QtWidgets
import sys
import os
import pandas as pd

class Ui_MainWindow(QtWidgets.QWidget):
    def __init__(self):
        super().__init__()
        self.desktop_path = os.path.join(os.path.expanduser("~"),'Desktop')
        self.setupUi(self)
        self.excel_path = None
        self.all_sheet_name = None
        self.current_sheet = None
        self.all_data = None
        self.data = None
    def setupUi(self, MainWindow):
        MainWindow.setObjectName("mainWindow")
        MainWindow.setWindowTitle('数据去冗余')
        MainWindow.resize(657, 675)
        MainWindow.setFixedSize(657, 675)
        self.centralwidget = QtWidgets.QWidget(MainWindow)
        self.centralwidget.setObjectName("centralwidget")
        self.listWidget = QtWidgets.QListWidget(self.centralwidget)
        self.listWidget.setGeometry(QtCore.QRect(80, 130, 181, 261))
        self.listWidget.setObjectName("listWidget")
        self.listWidget_2 = QtWidgets.QListWidget(self.centralwidget)
        self.listWidget_2.setGeometry(QtCore.QRect(370, 130, 181, 261))
        self.listWidget_2.setObjectName("listWidget_2")
        self.pushButton = QtWidgets.QPushButton(self.centralwidget)
        self.pushButton.setGeometry(QtCore.QRect(270, 220, 93, 28))
        self.pushButton.setText(">>")
        self.pushButton.clicked.connect(self.left_to_right)
        self.pushButton_2 = QtWidgets.QPushButton(self.centralwidget)
        self.pushButton_2.setGeometry(QtCore.QRect(270, 260, 93, 28))
        self.pushButton_2.setText("<<")
        self.pushButton_2.clicked.connect(self.right_to_left)
        self.pushButton_3 = QtWidgets.QPushButton(self.centralwidget)
        self.pushButton_3.setGeometry(QtCore.QRect(40, 50, 131, 31))
        self.pushButton_3.setText("选择Excel文件")
        self.pushButton_3.clicked.connect(self.getExcelPath)
        self.lineEdit = QtWidgets.QLineEdit(self.centralwidget)
        self.lineEdit.setGeometry(QtCore.QRect(180, 50, 351, 31))
        self.lineEdit.setObjectName("lineEdit")
        self.pushButton_4 = QtWidgets.QPushButton(self.centralwidget)
        self.pushButton_4.setGeometry(QtCore.QRect(540, 50, 91, 31))
        self.pushButton_4.setText("导入表格")
        self.pushButton_4.clicked.connect(self.importSheetName)
        self.textBrowser = QtWidgets.QTextBrowser(self.centralwidget)
        self.textBrowser.setGeometry(QtCore.QRect(80, 420, 481, 111))
        self.textBrowser.setObjectName("textBrowser")
        self.pushButton_5 = QtWidgets.QPushButton(self.centralwidget)
        self.pushButton_5.setGeometry(QtCore.QRect(240, 570, 151, 51))
        self.pushButton_5.setText("执行")
        self.pushButton_5.clicked.connect(self.function)
        self.combox = QtWidgets.QComboBox(self.centralwidget)
        self.combox.setGeometry(QtCore.QRect(80, 90, 181, 30))
        self.pushButton_6 = QtWidgets.QPushButton(self.centralwidget)
        self.pushButton_6.setGeometry(QtCore.QRect(370, 90, 181, 30))
        self.pushButton_6.setText("导入标题数据")
        self.pushButton_6.clicked.connect(self.importTitle) 
        self.menubar = QtWidgets.QMenuBar(MainWindow)
        self.menubar.setGeometry(QtCore.QRect(0, 0, 657, 26))
        self.menubar.setObjectName("menubar")
        self.file = self.menubar.addMenu('帮助')
        self.file.addAction('查看操作文本')
        self.file.triggered[QtWidgets.QAction].connect(self.processtrigger)
        

#        self.retranslateUi(MainWindow)
#        QtCore.QMetaObject.connectSlotsByName(MainWindow)

    def processtrigger(self,qaction):
        self.help_doc_path = os.path.join(os.getcwd(),'help.docx')
        if os.path.exists(self.help_doc_path):
            os.system(self.help_doc_path)
        else:
            QtWidgets.QMessageBox.warning(self,'提示',"说明文档丢失")

    def retranslateUi(self, MainWindow):
        _translate = QtCore.QCoreApplication.translate
        MainWindow.setWindowTitle(_translate("epandas1.0", "epandas1.0"))

        
    def getExcelPath(self):
        self.excel_path = QtWidgets.QFileDialog.getOpenFileName(None,'选择需要处理的excel文件',self.desktop_path)[0]
        self.lineEdit.setText(self.excel_path)
    
    def importSheetName(self):
        if self.excel_path == None:
            warning1 = QtWidgets.QMessageBox.warning(self,'提示',"请选择或输入正确的文件路径")
        else:
            try:
                self.all_data = pd.read_excel(self.excel_path,sheet_name=None)
                self.all_sheet_name = list(self.all_data.keys())
                if self.all_sheet_name == None:
                    warning2 = QtWidgets.QMessageBox.warning(self,'提示',"获取Excel表失败")
                else:
                    self.combox.addItems(self.all_sheet_name)
            except Exception as e:
                importSheetName_errInf = str(e)
                self.textBrowser.append(importSheetName_errInf)
                
    def importTitle(self):
        if self.all_sheet_name == None:
            warning = QtWidgets.QMessageBox.warning(self,'提示',"请先导入表格")
            return
        self.current_sheet = self.combox.currentText()
        try:

            if self.current_sheet == None:
                warning1 = QtWidgets.QMessageBox.warning(self,'提示',"请先导入表格数据")
            else:
                self.data = self.all_data[self.current_sheet]
                self.data_columns = list(self.data.columns)
                self.listWidget.addItems(self.data_columns)
        except Exception as e:
            importTitle_errInf = str(e)
            self.textBrowser.append(importTitle_errInf)  
            
    def function(self):
#        try:
        if len(self.data) == 0:
            warning3 = QtWidgets.QMessageBox.warning(self,'提示',"请先导入数据")
        else:
            self.selected_QList = []
            self.listWidget_2_count = self.listWidget_2.count()
            for num in range(self.listWidget_2_count):
                self.selected_QList.append(self.listWidget_2.item(num).text())
            comb = []
            for i in range(len(self.data)):
                i_str = ''
                for col in self.selected_QList:
                    i_col_data = self.data[col].iloc[i]
                    if type(i_col_data) == float:
                        i_col_data = round(i_price,3)
                    i_str = i_str +'_'+ str(i_col_data)
                comb.append(i_str)
            self.data['comb'] = comb
            newdata = self.data.drop_duplicates('comb',keep='first')
            newdata.sort_values('comb',inplace=True)
            newdata = newdata.drop(['comb'],axis=1)
            save_path = os.path.join(self.desktop_path,'output.xlsx')
            newdata.to_excel(save_path,index=None)
            self.textBrowser.append('执行完成!,输出文件路径为:%s'%(save_path))  
#        except Exception as e:
#            function_errInf = str(e)
#            self.textBrowser.append(function_errInf)  
            
    def left_to_right(self):
        try:
            item = QtWidgets.QListWidgetItem(self.listWidget.currentItem())
            item2 = self.listWidget.currentItem()
            if not item2 == None:
                self.listWidget_2.addItem(item)
            self.listWidget.takeItem(self.listWidget.row(item2))
        except Exception as e:
            left_to_right_errInf = str(e)
            self.textBrowser.append(left_to_right_errInf)  
            
    def right_to_left(self):
        try:
            item = QtWidgets.QListWidgetItem(self.listWidget_2.currentItem())
            item2 = self.listWidget_2.currentItem()
            if not item2 == None:
                self.listWidget.addItem(item)
            self.listWidget_2.takeItem(self.listWidget_2.row(item2))
        except Exception as e:
            right_to_left_errInf = str(e)
            self.textBrowser.append(right_to_left_errInf) 
            
            
            
class MyWindow(QtWidgets.QDialog,Ui_MainWindow):
      def __init__(self):
          super(MyWindow,self).__init__()
          self.setupUi(self)
        
if __name__ == '__main__':
    app = QtWidgets.QApplication(sys.argv)
    mywindow = MyWindow()
    mywindow.show()
    sys.exit(app.exec_())       
        
        
        
        
        
        
        
        
        
        
        
        
        
        
        